#!/usr/bin/env python3
"""
初始化 Supabase 数据库脚本
"""

import asyncio
import asyncpg
from app.core.config import settings

# Supabase 连接字符串 - 使用 service_role 密码
SUPABASE_DB_URL = "postgresql://postgres.fmoxcwchmixblrepfsxx:eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImZtb3hjd2NobWl4YmxyZXBmc3h4Iiwicm9sZSI6InNlcnZpY2Vfcm9sZSIsImlhdCI6MTc1NDc0OTg5OSwiZXhwIjoyMDcwMzI1ODk5fQ.7AyCUda-BQJD66QHhAi37aZ9OhqvPFr6F0GSyNIhYF8@aws-0-ap-southeast-1.pooler.supabase.com:6543/postgres"

async def create_competitions_table():
    """创建竞赛表和初始数据"""
    conn = await asyncpg.connect(SUPABASE_DB_URL)
    
    try:
        # 创建竞赛表
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS competitions (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                title VARCHAR(255) NOT NULL,
                description TEXT,
                platform VARCHAR(100) NOT NULL,
                prize_pool INTEGER DEFAULT 0,
                currency VARCHAR(10) DEFAULT 'CNY',
                deadline TIMESTAMP WITH TIME ZONE NOT NULL,
                participants_count INTEGER DEFAULT 0,
                max_participants INTEGER,
                difficulty VARCHAR(20) DEFAULT 'medium',
                category VARCHAR(100),
                tags TEXT[],
                image_url TEXT,
                external_url TEXT,
                status VARCHAR(20) DEFAULT 'active',
                created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
                updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
            );
        """)
        
        # 创建索引
        await conn.execute("CREATE INDEX IF NOT EXISTS idx_competitions_platform ON competitions(platform);")
        await conn.execute("CREATE INDEX IF NOT EXISTS idx_competitions_deadline ON competitions(deadline);")
        await conn.execute("CREATE INDEX IF NOT EXISTS idx_competitions_status ON competitions(status);")
        await conn.execute("CREATE INDEX IF NOT EXISTS idx_competitions_category ON competitions(category);")
        
        # 启用 RLS
        await conn.execute("ALTER TABLE competitions ENABLE ROW LEVEL SECURITY;")
        
        # 创建策略
        await conn.execute("""
            DROP POLICY IF EXISTS "Allow public read access" ON competitions;
            CREATE POLICY "Allow public read access" ON competitions
                FOR SELECT USING (true);
        """)
        
        await conn.execute("""
            DROP POLICY IF EXISTS "Allow authenticated users to insert" ON competitions;
            CREATE POLICY "Allow authenticated users to insert" ON competitions
                FOR INSERT WITH CHECK (true);
        """)
        
        # 授权
        await conn.execute("GRANT SELECT ON competitions TO anon;")
        await conn.execute("GRANT ALL PRIVILEGES ON competitions TO authenticated;")
        
        # 检查是否已有数据
        count = await conn.fetchval("SELECT COUNT(*) FROM competitions;")
        
        if count == 0:
            # 插入测试数据
            await conn.execute("""
                INSERT INTO competitions (title, description, platform, prize_pool, deadline, participants_count, difficulty, category, tags, image_url, external_url) VALUES
                ('2024 AI 创新挑战赛', '探索人工智能在各个领域的创新应用，打造下一代智能解决方案', 'Kaggle', 50000, '2024-03-15 23:59:59+00', 1250, 'hard', 'AI/ML', ARRAY['AI', '机器学习', '深度学习'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=AI%20innovation%20challenge%20competition%20poster%20with%20futuristic%20technology%20elements&image_size=landscape_4_3', 'https://www.kaggle.com/competitions/ai-innovation-2024'),
                
                ('全栈开发马拉松', '48小时内构建完整的全栈应用，展示你的编程技能和创意思维', 'DevPost', 30000, '2024-02-28 23:59:59+00', 890, 'medium', 'Web开发', ARRAY['全栈', 'React', 'Node.js'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=full%20stack%20development%20hackathon%20coding%20marathon%20programming&image_size=landscape_4_3', 'https://devpost.com/hackathons'),
                
                ('区块链创新大赛', '利用区块链技术解决现实世界问题，推动去中心化应用发展', 'GitHub', 80000, '2024-04-20 23:59:59+00', 567, 'hard', '区块链', ARRAY['区块链', 'DeFi', 'Web3'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=blockchain%20innovation%20competition%20cryptocurrency%20decentralized%20technology&image_size=landscape_4_3', 'https://github.com/blockchain-hackathon'),
                
                ('移动应用设计挑战', '设计并开发创新的移动应用，提升用户体验和生活质量', 'App Store', 25000, '2024-03-10 23:59:59+00', 1100, 'medium', '移动开发', ARRAY['iOS', 'Android', 'Flutter'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=mobile%20app%20design%20challenge%20smartphone%20user%20interface&image_size=landscape_4_3', 'https://developer.apple.com/app-store/'),
                
                ('数据科学竞赛', '分析复杂数据集，发现隐藏的模式和洞察，解决商业问题', 'Kaggle', 40000, '2024-03-25 23:59:59+00', 2300, 'medium', '数据科学', ARRAY['数据分析', 'Python', '机器学习'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=data%20science%20competition%20analytics%20charts%20graphs%20statistics&image_size=landscape_4_3', 'https://www.kaggle.com/competitions'),
                
                ('游戏开发挑战赛', '创造令人惊叹的游戏体验，展示你的创意和技术实力', 'Unity', 35000, '2024-04-05 23:59:59+00', 750, 'hard', '游戏开发', ARRAY['Unity', 'C#', '游戏设计'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=game%20development%20challenge%20video%20games%20programming%20unity&image_size=landscape_4_3', 'https://unity.com/competitions'),
                
                ('网络安全挑战', '测试你的网络安全技能，发现并修复安全漏洞', 'HackerOne', 60000, '2024-03-30 23:59:59+00', 450, 'hard', '网络安全', ARRAY['安全', '渗透测试', 'CTF'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=cybersecurity%20challenge%20hacking%20security%20shield%20protection&image_size=landscape_4_3', 'https://www.hackerone.com/'),
                
                ('UI/UX 设计大赛', '设计出色的用户界面和用户体验，提升产品的可用性', 'Dribbble', 20000, '2024-02-25 23:59:59+00', 980, 'easy', 'UI/UX', ARRAY['设计', 'Figma', '用户体验'], 'https://trae-api-us.mchost.guru/api/ide/v1/text_to_image?prompt=UI%20UX%20design%20competition%20user%20interface%20creative%20design&image_size=landscape_4_3', 'https://dribbble.com/shots');
            """)
            
            print("✅ 成功插入测试数据")
        else:
            print(f"ℹ️ 数据库中已有 {count} 条竞赛记录")
        
        print("✅ 数据库初始化完成")
        
    except Exception as e:
        print(f"❌ 数据库初始化失败: {e}")
        raise
    finally:
        await conn.close()

if __name__ == "__main__":
    asyncio.run(create_competitions_table())